import java.io.*;
import java.sql.*;
import java.util.*;

/**
@Auther: MFM
@Date: 2021/10/17 - 10 - 17 - 15:32 
@version: 1.0
*/

public class Test {
    private static class Teacher{
        private Integer id;
        private String userName;
        private String gender;
        private Integer age;
        private String teacherCode;
        private String nation;
        private Integer maritalStatus;
        private String email;

        public Integer getId() {
            return id;
        }

        public void setId(Integer id) {
            this.id = id;
        }

        public String getUserName() {
            return userName;
        }

        public void setUserName(String userName) {
            this.userName = userName;
        }

        public String getGender() {
            return gender;
        }

        public void setGender(String gender) {
            this.gender = gender;
        }

        public Integer getAge() {
            return age;
        }

        public void setAge(Integer age) {
            this.age = age;
        }

        public String getTeacherCode() {
            return teacherCode;
        }

        public void setTeacherCode(String teacherCode) {
            this.teacherCode = teacherCode;
        }

        public String getNation() {
            return nation;
        }

        public void setNation(String nation) {
            this.nation = nation;
        }

        public Integer getMaritalStatus() {
            return maritalStatus;
        }

        public void setMaritalStatus(Integer maritalStatus) {
            this.maritalStatus = maritalStatus;
        }

        public String getEmail() {
            return email;
        }

        public void setEmail(String email) {
            this.email = email;
        }

        public Teacher(Integer id, String userName, String gender, Integer age, String teacherCode, String nation, Integer maritalStatus, String email) {
            this.id = id;
            this.userName = userName;
            this.gender = gender;
            this.age = age;
            this.teacherCode = teacherCode;
            this.nation = nation;
            this.maritalStatus = maritalStatus;
            this.email = email;
        }

        @Override
        public String toString() {
            return "Teacher{" +
                    "id=" + id +
                    ", userName='" + userName + '\'' +
                    ", gender='" + gender + '\'' +
                    ", age=" + age +
                    ", teacherCode='" + teacherCode + '\'' +
                    ", nation='" + nation + '\'' +
                    ", maritalStatus=" + maritalStatus +
                    ", email='" + email + '\'' +
                    '}';
        }
    }


    public static void f1() throws Exception{


        Class.forName("com.mysql.cj.jdbc.Driver");
        // url = "jdbc:mysql://127.0.1:端口号/数据库名?数据库文字格式&&&时区=Asia/Shanghai&"
        String url ="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        Connection conn = DriverManager.getConnection(url, "root", "52zjx1314");
        //根据链接对象，创建一个能执行sql语句命令的对象
        Statement statement = conn.createStatement();
        //写一条sql语句进行执行
//        String sql = "select * from teacher";
//        //返回一个boolean类型的值，如果值是true，表名是查询操作，如果是false表名是其他类型的操作
//        boolean flag = statement.execute(sql);
//        if(flag){
//            List<Teacher> list = new ArrayList<>();
//            //获取查询结果集合
//            ResultSet set = statement.getResultSet();
//            while (set.next()){
//                //条件成立，查询结果集的记录指针，指向了查询结果集中的一条记录
//                Integer id = set.getInt("id");
//                String userName = set.getString("user_name");
//                String gender = set.getString("gender");
//                int age = set.getInt("age");
//                String teacherCode = set.getString("teacher_code");
//                String nation = set.getString("nation");
//                int maritalStatus = set.getInt("marital_status");
//                String email = set.getString("email");
//                Teacher teacher = new Teacher(id,userName,gender,age,teacherCode,nation,maritalStatus,email);
//                list.add(teacher);
//            }
//            for (Teacher teacher : list) {
//                System.out.println(teacher);
//            }
//        }

//        File file = new File("D:\\IT\\大二第一学期\\MySQL中文手册.chm");
//        String fileName = file.getName();
//        String type = fileName.substring(fileName.indexOf(".")+1);
//        InputStream stream = new FileInputStream(file);
//        String sql = "insert into file value(null,?,?)";
//        PreparedStatement ps = conn.prepareStatement(sql);
//        ps.setBinaryStream(1,stream);
//        ps.setString(2,type);
//        int row = ps.executeUpdate();
//        System.out.println(row);

//        String sql = "select * from file ";
//        PreparedStatement ps = conn.prepareStatement(sql);
//        ResultSet rs = ps.executeQuery();
//        rs.next();
//        String type = rs.getString("type");
//        Blob data = rs.getBlob("data");
//        InputStream stream = data.getBinaryStream();
//        OutputStream out = new FileOutputStream("D:/"+System.currentTimeMillis()+"."+type);
//        byte[] bytes = new byte[1024];
//        int size = -1;
//        while ((size = stream.read(bytes))>0){
//            out.write(bytes,0,size);
//        }
//        out.close();

        String sql = "select * from teacher";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int colNum = metaData.getColumnCount();
        for (int i = 1; i < colNum; i++) {
            System.out.println(metaData.getColumnName(i));
            System.out.println(metaData.getColumnType(i));
        }


        //最后不要忘记关闭链接
        conn.close();

    }

    public static void main(String[] args) throws Exception{
        String sql = "select * from teacher";
        List<Map<String,Object>> list = queryData(sql);
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> row = list.get(i);
            System.out.println(row);
        }
    }

    public static List<Map<String,Object>> queryData(String sql)throws Exception{
        List<Map<String,Object>> list = new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        // url = "jdbc:mysql://127.0.1:端口号/数据库名?数据库文字格式&&&时区=Asia/Shanghai&"
        String url ="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        Connection conn = DriverManager.getConnection(url, "root", "52zjx1314");
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet set = ps.executeQuery();
        ResultSetMetaData metaData = set.getMetaData();
        while (set.next()) {
            Map<String,Object> row = new HashMap<>();
            int count = metaData.getColumnCount();
            for (int i = 1; i < count; i++) {
                String name = metaData.getColumnName(i);
                Integer type = metaData.getColumnType(i);
                Object value = getColumnValue(type,name,set);
                row.put(name,value);
                list.add(row);
            }
        }
        conn.close();
        return list;
    }

    private static Object getColumnValue(int type,String name,ResultSet set)throws  Exception{
        Object value = null;
        switch (type){
            case Types.INTEGER:
                value = set.getInt(name);
                break;
            case Types.FLOAT:
                value = set.getFloat(name);
                break;
            case Types.DOUBLE:
                value = set.getDouble(name);
                break;
            case Types.DATE:
                value = set.getDate(name);
                break;
            default:
                value = set.getString(name);
                break;
        }
        return value;
    }
}
